class Designer < ActiveRecord::Base

  belongs_to :department
  has_many :designer_work_items, :dependent => :destroy

  validates_presence_of :full_name
  validates_uniqueness_of :full_name

  def exist?(args = {})
    args[:designer_id] = id
    DesignerWorkItem.exists? [
        'stage_id = :stage_id and work_item_type_id = :work_item_type_id and designer_id = :designer_id',
        args
      ]
  end

  def self.all_by_stage_id_and_work_item_type_id(stage_id,work_item_type_id)
    all :conditions => [
      'designer_work_items.stage_id = ? and designer_work_items.work_item_type_id = ? ',
      stage_id,
      work_item_type_id
    ],
    :include => :designer_work_items
  end

  def designer_work_items_by(args = {})
    args[:designer_id] = id
    if args[:project_id] && args[:pay_status]
      DesignerWorkItem.all :conditions => [
        'designer_id = :designer_id and projects.id = :project_id and pay_status = :pay_status',
        args
      ],
        :include => {
          :stage => :project
        } ,
        :order => 'designer_work_items.created_at asc'
    elsif args[:work_item_id]
      DesignerWorkItem.all :conditions => [
        'designer_id = :designer_id  and work_item_id = :work_item_id',
        args
      ],
        :order => 'created_at asc'
    elsif args[:project_id]
      DesignerWorkItem.all :conditions => [
        'designer_id = :designer_id and projects.id = :project_id',
        args
      ],
        :include => {
          :stage => :project
        } ,
        :order => 'designer_work_items.created_at asc'
    else 
      DesignerWorkItem.all :conditions => [
        'designer_id = :designer_id and work_item_type_id = :work_item_type_id and stage_id = :stage_id',
        args
      ],
        :order => 'created_at asc'
    end
  end

  def paying_total_amount
    DesignerWorkItem.sum(:should_be_payed,
      :conditions => [
        'designer_id = ? and stages.pay_status = ? ',
        id,
        'paying'
      ],
      :include => :stage
    )
  end

  def paying_projects_count
    Project.count :conditions => [
      'stages.pay_status = ? and designer_work_items.designer_id = ?',
      'paying',
      id
    ],
    :include => {
      :stages => :designer_work_items
    }
  end

  def paused_total_amount
    DesignerWorkItem.sum(:should_be_payed,
      :conditions => [
        'designer_id = ? and stages.pay_status = ? ',
        id,
        'paused'
      ],
      :include => :stage
    )
  end

  def paused_projects_count
    Project.count :conditions => [
      'stages.pay_status = ? and designer_work_items.designer_id = ?',
      'paused',
      id
    ],
    :include => {
      :stages => :designer_work_items
    }
  end

  def total_amount_by_pay_status(pay_status)
    case pay_status
    when 'paying'
      paying_total_amount
    when 'payed'
      payed__total_amount
    when 'paused'
      paused_total_amount
    else
      raise "未知#{pay_status}"
    end
  end

  def projects_by_pay_status(pay_status)
    case pay_status
    when 'paying'
      paying_projects
    when 'payed'
      payed_projects
    when 'paused'
      paused_projects
    else
      raise "未知#{pay_status}"
    end
  end

  def payed_total_amount
    DesignerWorkItem.sum(:should_be_payed,
      :conditions => [
        'designer_id = ? and stages.pay_status = ? ',
        id,
        'payed'
      ],
      :include => :stage
    )
  end

  def total_amount
    DesignerWorkItem.sum(:should_be_payed,
      :conditions => [
        'designer_id = ? ',
        id
      ]
    )
  end

  def payed_projects_count
    Project.count :conditions => [
      'stages.pay_status = ? and designer_work_items.designer_id = ?',
      'payed',
      id
    ],
    :include => {
      :stages => :designer_work_items
    }
  end

  def projects_count
    projects.size
  end

  def should_be_payed
    DesignerWorkItem.sum(:should_be_payed,
      :conditions => [
        'designer_id = ? ',
        id,
      ]
    )
  end

  def payed_projects
    Project.all :conditions => [
      'stages.pay_status = ? and designer_work_items.designer_id = ?',
      'payed',
      id
    ],
    :include => {
      :stages => :designer_work_items
    }
  end

  def paying_projects
    Project.all :conditions => [
      'stages.pay_status = ? and designer_work_items.designer_id = ?',
      'paying',
      id
    ],
    :include => {
      :stages => :designer_work_items
    }
  end

  def paused_projects
    Project.all :conditions => [
      'stages.pay_status = ? and designer_work_items.designer_id = ?',
      'paused',
      id
    ],
    :include => {
      :stages => :designer_work_items
    }
  end

  def projects(args = {})
    if args.empty?
      Project.all :conditions => [
        'designer_work_items.designer_id = ?',
        id
      ],
      :include => {
        :stages => :designer_work_items
      }
    else
      args[:designer_id] = id
      Project.all :conditions => [
        'designer_work_items.designer_id = :designer_id and stages.payed_on >= :started_on and stages.payed_on <= :end_on',
        args
      ],
      :include => {
        :stages => :designer_work_items
      }
    end
  end

  def paying_amount_by_project_id(project_id)
    DesignerWorkItem.sum(:should_be_payed,
      :conditions => [
        'designer_id = ? and stages.project_id = ? and stages.pay_status = ?  ',
        id,
        project_id,
        'paying'
      ],
      :include => :stage
    )
  end

  def payed_amount_by_project_id(project_id)
    DesignerWorkItem.sum(:should_be_payed,
      :conditions => [
        'designer_id = ? and stages.project_id = ? and stages.pay_status = ?  ',
        id,
        project_id,
        'payed'
      ],
      :include => :stage
    )
  end

  def paused_amount_by_project_id(project_id)
    DesignerWorkItem.sum(:should_be_payed,
      :conditions => [
        'designer_id = ? and stages.project_id = ? and stages.pay_status = ?  ',
        id,
        project_id,
        'paused'
      ],
      :include => :stage
    )
  end

  def should_be_payed_by_stage_id(stage_id)
    DesignerWorkItem.sum(:should_be_payed,
      :conditions => [
        'designer_id = ? and stage_id = ?',
        id,
        stage_id
      ]
    )
  end

  def work_item_types_by_stage_id(stage_id)
    WorkItemType.all :conditions => [
      'designer_work_items.stage_id = ? and designer_work_items.designer_id = ?',
      stage_id,
      id
    ],
    :include => :designer_work_items
  end

  def designer_work_items_by_stage_id_and_work_item_type_id(stage_id,work_item_type_id)
    DesignerWorkItem.all :conditions => [
      'stage_id = ? and work_item_type_id = ? and designer_id = ?',
      stage_id,
      work_item_type_id,
      id
    ]
  end

  def should_be_payed_by_stage_id_and_work_item_type_id(stage_id, work_item_type_id)
    DesignerWorkItem.sum :should_be_payed,
      :conditions => [
        'stage_id = ? and work_item_type_id = ? and designer_id = ? ',
        stage_id,
        work_item_type_id,
        id
      ]
  end
  
  def stat_by_payed_on
    Designer.find_by_sql [ '
        select strftime("%Y",stages.payed_on) as year,
                count(DISTINCT(stages.project_id)) as has_payed_projects_count, 
                sum(designer_work_items.should_be_payed) as has_payed_total_amount
        from stages, designer_work_items
        where stages.pay_status = ? 
            and designer_work_items.designer_id = ? 
            and designer_work_items.stage_id = stages.id
        group by strftime("%Y",stages.payed_on)
        order by strftime("%Y",stages.payed_on) desc
    ', 
    'payed',
     id
    ]
  end

end

